package com.lovo.total.dao.impl;

import com.lovo.common.dao.BaseDao;
import com.lovo.common.model.AreaTotalBean;
import com.lovo.common.model.CutPageBean;
import com.lovo.common.model.LogBean;
import com.lovo.total.dao.IAreaTotalDao;

import java.sql.SQLException;

/**
 * 地区统计的Dao实现类
 * @author 白德柯
 *
 */
public class AreaTotalDaoImpl extends BaseDao implements IAreaTotalDao {
    @Override
    public CutPageBean<AreaTotalBean> findByItem(int page) {
        String sql = "SELECT dict_value city_name,COUNT(s.city_id) school_count,\n" +
                "(SELECT COUNT(*) FROM school s1 WHERE s1.city_id=sys.id AND s1.status=4) in_contact_count,\n" +
                "(SELECT COUNT(*) FROM school s1 WHERE s1.city_id=sys.id AND s1.status=5) pending_count,\n" +
                "(SELECT COUNT(*) FROM school s1 WHERE s1.city_id=sys.id AND s1.status=6) passed_count,\n" +
                "(SELECT COUNT(*) FROM school s1 WHERE s1.city_id=sys.id AND s1.status=7) failed_count\n" +
                "FROM school s\n" +
                "RIGHT JOIN sys_dict sys ON s.city_id=sys.id\n" +
                "WHERE sys.type_id=1004\n" +
                "GROUP BY sys.id,dict_value";

        CutPageBean<AreaTotalBean> cutPageBean = new CutPageBean<>();
        this.setConnection();
        try {
            ps = con.prepareStatement(sql + CutPageBean.getPageSql(page));
            rs = ps.executeQuery();
            while(rs.next()){
                cutPageBean.getList().add(fill());
            }
            ps = con.prepareStatement("SELECT COUNT(*) cnt FROM ("+ sql +") t");
            rs = ps.executeQuery();
            rs.next();
            // 获取员工总数并设置给分页对象
            cutPageBean.setCount(rs.getInt("cnt"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return cutPageBean;
    }

    private AreaTotalBean fill() throws SQLException {
        AreaTotalBean areaTotalBean = new AreaTotalBean();
        areaTotalBean.setCityName(rs.getString("city_name"));
        areaTotalBean.setSchoolCount(rs.getInt("school_count"));
        areaTotalBean.setInContactCount(rs.getInt("in_contact_count"));
        areaTotalBean.setPendingCount(rs.getInt("pending_count"));
        areaTotalBean.setPassedCount(rs.getInt("passed_count"));
        areaTotalBean.setFailedCount(rs.getInt("failed_count"));
        return areaTotalBean;
    }

    public static void main(String[] args) {
        IAreaTotalDao areaTotalDao = new AreaTotalDaoImpl();
        System.out.println(areaTotalDao.findByItem(1));
    }
}
